package com.hnkywl.sts.dao.sfgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.htgl.Cght;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.entity.sfgl.Sfk;
import com.hnkywl.sts.entity.xsgl.Xsd;
import com.hnkywl.sts.entity.xsgl.Xsmx;
import com.hnkywl.sts.service.sys.YgService;
import com.hnkywl.sts.util.CalcUtils;
import com.hnkywl.sts.util.DateUtil;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: cyh
 * Date: 13-4-19
 * Time: 上午10:40
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class SkshDao extends SimpleHibernateTemplate<Sfk, Long> {
    /**
     * 查询付款审核
     *
     * @param sbgsMc
     * @param nbgsId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Sfk> findSklxList(Long kxsy, Date skshStart, Date skshEnd, String bm, String sbgsMc, Long nbgsId, String gldwMc, String zt, String skr, int pageNo, int pageSize) {
        String hql;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Sfk> page = new Page<Sfk>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (kxsy != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" kxsy.id = ? ");
            param.add(kxsy);
        }
        if (bm != null && !bm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" bm like ? ");
            param.add("%" + bm.trim() + "%");
        }
        if (skr != null && !skr.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" createUser.xm like ? ");
            param.add("%" + skr.trim() + "%");
        }
        if (gldwMc != null && !gldwMc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gldw.qc like ? ");
            param.add("%" + gldwMc.trim() + "%");
        }
        if (zt != null && !zt.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ? ");
            param.add(zt);
        }
        if (nbgsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.id = ? ");
            param.add(nbgsId);
        }
        if (skshStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" dateCreated >= ? ");
            param.add(sDaysAfterNowDate(skshStart));
        }
        if (skshEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" dateCreated < ? ");
            param.add(jDaysAfterNowDate(skshEnd));
        }
        if (nbgsId == null && sbgsMc != null && !sbgsMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.jc like ? ");
            param.add("%" + sbgsMc.trim() + "%");
        }

        //==============记录数量===============
        hql = "select count(*) from Sfk where sfklx in (3,4) " +
                " and (createUser.id is not null )  and nbhzsfk is null "
                + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Sfk where sfklx in (3,4) " +

                " and nbhzsfk is null and (createUser.id is not null ) "

                + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by lastUpdated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public String findAllSksh() {
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        String hql = "select count(*) from Sfk where sfklx in (3,4) and zt='ybc' " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = (Long) findUnique(hql);
        return c.toString();
    }

    public Sfk findSfkByBm(String sfkBm) {
        String hql = " from Sfk where bm = ? ";
        return this.find(hql, sfkBm).size() <= 0 ? null : (Sfk) this.find(hql, sfkBm).get(0);
    }

    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }


    public Page<Sfk> findskshByCghtList(Long cghtId, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Sfk> page = new Page<Sfk>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (cghtId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" ht.id = ? ");
            param.add(cghtId);
        }
        //==============记录数量===============
        hql = "select count(*) from Sfk where sfklx in (3,4) ";
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Sfk where sfklx in (3,4)";
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + "order by sfkrq desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    /**
     * 判断属性是否重复
     *
     * @param id            收款审核id
     * @param propertyName  收款审核属性名
     * @param propertyValue 收款审核属性值
     * @return
     */
    public Boolean checkUnique(Long id, String propertyName, Object propertyValue) {
        String hql;
        if (propertyValue == null)
            return false;
        if (id == null) {
            //修改检验
            hql = "from Sfk where sfklx in (3,4) and " + propertyName + " = ? ";
            return this.existsQuery(hql, propertyValue);
        } else {
            //新增检验
            hql = "from Sfk where sfklx in (3,4) and " + propertyName + " = ? and id <>?";
            return this.existsQuery(hql, propertyValue, id);
        }
    }

    public Page findBzjSkXsmsList(Long gldwId, Integer fplx, int pageNo, int pageSize) {
        Page<Sfk> page = new Page<Sfk>();
        List<String> list = new ArrayList<String>();
        list.add("竞价保证金（普通）");
        list.add("竞价保证金（长协）");
        list.add("年度协议保证金");
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kxsy", "kxsy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fkfs", "fkfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gldw", "gldw", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("bm").as("bm"))
                        .add(Projections.sqlProjection("this_.ye as ye", new String[]{"ye"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.fplx as fplx", new String[]{"fplx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sfje").as("sfje"))
                        .add(Property.forName("sfkrq").as("sfkrq"))
                        .add(Property.forName("fkfs.z").as("fkfs.z"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gldwId != null)
            criteria.add(Restrictions.eq("gldw.id", gldwId));

        criteria.add(Restrictions.in("kxsy.z", list));
        criteria.add(Restrictions.eq("zt", "ytz"));
        criteria.add(Restrictions.eq("fplx", fplx));
        criteria.add(Restrictions.gt("ye", 0.0));

        return this.findByCriteria(page, criteria);
    }

    public Page findBzjSkList(Long gldwId, Integer fplx, int pageNo, int pageSize) {
        Page<Sfk> page = new Page<Sfk>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kxsy", "kxsy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fkfs", "fkfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gldw", "gldw", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("bm").as("bm"))
                        .add(Projections.sqlProjection("this_.ye as ye", new String[]{"ye"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.fplx as fplx", new String[]{"fplx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sfje").as("sfje"))
                        .add(Property.forName("sfkrq").as("sfkrq"))
                        .add(Property.forName("fkfs.z").as("fkfs.z"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gldwId != null)
            criteria.add(Restrictions.eq("gldw.id", gldwId));

        criteria.add(Restrictions.eq("kxsy.z", "合同保证金"));
        criteria.add(Restrictions.eq("zt", "ytz"));
        criteria.add(Restrictions.eq("fplx", fplx));
        criteria.add(Restrictions.gt("ye", 0.0));

        return this.findByCriteria(page, criteria);
    }

    public Page findHthkSkList(Long gldwId, Integer fplx, int pageNo, int pageSize) {
        Page<Sfk> page = new Page<Sfk>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kxsy", "kxsy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fkfs", "fkfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gldw", "gldw", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("bm").as("bm"))
                        .add(Property.forName("sfje").as("sfje"))
                        .add(Projections.sqlProjection("this_.ye as ye", new String[]{"ye"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.fplx as fplx", new String[]{"fplx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sfkrq").as("sfkrq"))
                        .add(Property.forName("fkfs.z").as("fkfs.z"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gldwId != null) {
            criteria.add(Restrictions.eq("gldw.id", gldwId));
        }
        criteria.add(Restrictions.eq("kxsy.z", "货款"));
        criteria.add(Restrictions.eq("zt", "ytz"));
        criteria.add(Restrictions.eq("fplx", fplx));
        criteria.add(Restrictions.gt("ye", 0.0));

        return this.findByCriteria(page, criteria);
    }


    /**
     * 查询按比例释放的货款
     *
     * @param gldwId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page findProrateSkList(Long gldwId, Integer fplx, int pageNo, int pageSize) {
        List<String> ids = new ArrayList<String>();
        ids.add("货款");
        ids.add("保证金");
        Page<Sfk> page = new Page<Sfk>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kxsy", "kxsy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fkfs", "fkfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gldw", "gldw", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("bm").as("bm"))
                        .add(Property.forName("sfje").as("sfje"))
                        .add(Projections.sqlProjection("this_.ye as ye", new String[]{"ye"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.fplx as fplx", new String[]{"fplx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sfkrq").as("sfkrq"))
                        .add(Property.forName("khfksj").as("khfksj"))
                        .add(Property.forName("tx").as("tx"))
                        .add(Property.forName("txye").as("txye"))
                        .add(Projections.sqlProjection("kxsy1_.z as kxsy", new String[]{"kxsy"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("fkfs.z").as("fkfs.z"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gldwId != null)
            criteria.add(Restrictions.eq("gldw.id", gldwId));

        criteria.add(Restrictions.or(Restrictions.eq("zt", "ytz"), Restrictions.eq("zt", "yjs")));
        criteria.add(Restrictions.gt("ye", 0.0));
        criteria.add(Restrictions.in("kxsy.z", ids));
        criteria.add(Restrictions.eq("fplx", fplx));
        criteria.add(Restrictions.isNull("xsd.id"));
        criteria.add(Restrictions.isNull("sfJsBzj"));
        criteria.addOrder(Property.forName("ye").asc());
        criteria.addOrder(Property.forName("sfkrq").asc());
        return this.findByCriteria(page, criteria);
    }


    public Page findXsdSkList(Xsd xsd, Long gldwId, Integer fplx, int pageNo, int pageSize) {
        List<String> ids = new ArrayList<String>();
        ids.add("货款");
        ids.add("代理费用");
        String bzjSkIds = this.findSKIDSBzjsWithOutLocks(xsd.getXsht().getId());
//        if (StringUtils.equals("xxms", xsd.getXsht().getHtms())) {
//            ids.add("合同保证金");
//        }
        Page<Sfk> page = new Page<Sfk>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kxsy", "kxsy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fkfs", "fkfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gldw", "gldw", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("bm").as("bm"))
                        .add(Property.forName("sfje").as("sfje"))
                        .add(Projections.sqlProjection("this_.ye as ye", new String[]{"ye"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.fplx as fplx", new String[]{"fplx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sfkrq").as("sfkrq"))
                        .add(Property.forName("khfksj").as("khfksj"))
                        .add(Projections.sqlProjection("kxsy1_.z as kxsy", new String[]{"kxsy"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("fkfs.z").as("fkfs.z"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gldwId != null)
            criteria.add(Restrictions.eq("gldw.id", gldwId));

        criteria.add(Restrictions.or(Restrictions.eq("zt", "ytz"), Restrictions.eq("zt", "yjs")));
        criteria.add(Restrictions.gt("ye", 0.0));
        criteria.add(Restrictions.in("kxsy.z", ids));
        criteria.add(Restrictions.eq("fplx", fplx));
        criteria.add(Restrictions.isNull("xsd.id"));
        criteria.add(Restrictions.isNull("sfJsBzj"));
        if (StringUtils.isNotEmpty(bzjSkIds)) {
            criteria.add(Restrictions.sqlRestriction(" 1 = 1 or this_.id in (" + bzjSkIds + ")"));
        }
        criteria.addOrder(Property.forName("sfkrq").asc());
        criteria.addOrder(Property.forName("fkfs.bm").asc());
        return this.findByCriteria(page, criteria);
    }

    /**
     * 查询所有解锁的保证金id
     *
     * @param xshtId
     * @return
     */
    public String findSKIDSBzjsWithOutLocks(Long xshtId) {
        String sql = "select sk.id from t_biz_bzj bzj ,t_biz_sfk sk where bzj.deleted = 0 and sk.deleted = 0 and bzj.status = 2 and bzj.sk_id = sk.id and bzj.ht_id = (select cght.id from t_biz_ht cght, t_biz_ht xsht where xsht.zddhcght_id = cght.id and xsht.id = ? ) order by sk.fkfs_sjzdz_id,sk.sfkrq ";
        List<Map> list = (List<Map>) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        StringBuffer ids = new StringBuffer();
        if (list.size() > 0) {
            for (Map map : list) {
                ids.append(map.get("ID").toString() + ",");
            }
        }
        if (ids.length() > 0) {
            ids.deleteCharAt(ids.length() - 1);
        }
        return ids.toString();
    }
    
    /**
     * 查询所有解锁的保证金
     *
     * @param xshtId
     * @return
     */
    public List<Sfk> findBzjsWithOutLocks(Long xshtId) {
        String sql = "select sk.id from t_biz_bzj bzj ,t_biz_sfk sk where bzj.deleted = 0 and sk.deleted = 0 and bzj.status = 2 and bzj.sk_id = sk.id and bzj.ht_id = (select cght.id from t_biz_ht cght, t_biz_ht xsht where xsht.zddhcght_id = cght.id and xsht.id = ? ) order by sk.fkfs_sjzdz_id,sk.sfkrq ";
        List<Map> list = (List<Map>) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        List<Sfk> sfks = new ArrayList<Sfk>();
        if (list.size() > 0) {
            for (Map map : list) {
                Sfk sfk = this.get(Long.valueOf(map.get("ID").toString()));
                sfks.add(sfk);
            }
        }
        return sfks;
    }


    /**
     * 查询保证金比例根据销售ID
     *
     * @param xsdId
     * @return
     */
    public List<Map> findBzjblFromXsdId(Long xsdId) {
        String sql = "select bzj.bzjbl from t_biz_bzj bzj where bzj.deleted = 0 and bzj.ht_id = (select xsht.zddhcght_id from t_biz_ht xsht where xsht.deleted = 0 and xsht.id = (select xsd.xsht_id from t_biz_xsd xsd where xsd.deleted = 0 and xsd.id = ?))";
        return (List<Map>) this.createSQLQuery(sql, xsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    /**
     * 代理费标准
     *
     * @param skfId
     * @param xsdId
     * @param tzfksj
     * @param fkfs
     * @return
     */
    public String executeDLFBZ(Long skfId, Long xsdId, String tzfksj, Long fkfs) {
        Connection con = null;
        CallableStatement call = null;
        String result;
        try {
            con = getSession().connection();
            System.out.print("R_BIZ_DLFBZ...");
            call = con.prepareCall("{Call R_BIZ_DLFBZ(?,?,?,?,?)}");
            call.setLong(1, skfId);
            call.setLong(2, xsdId);
            call.setString(3, tzfksj);
            call.setLong(4, fkfs);
            call.registerOutParameter(5, Types.VARCHAR);
            call.execute();
            result = call.getString(5);
            if ("计算代理费用标准成功".equals(result)) {
                System.out.print("R_BIZ_DLFBZ...");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(null, call, con);
        }
        return result;
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    /**
     * 配款操作，插入计费明细(按比例释放)
     *
     * @param xsdId
     * @param xshtId
     * @param khId
     * @param cghtId
     * @return
     */
    public String getPkResult(Long xsdId, Long xshtId, Long khId, Long cghtId) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            System.out.print("R_BIZ_ABLSFPK...");
            call = con.prepareCall("{Call R_BIZ_ABLSFPK(?,?,?,?,?)}");
            call.setLong(1, xsdId);
            call.setLong(2, xshtId);
            call.setLong(3, khId);
            call.setLong(4, cghtId);
            call.registerOutParameter(5, Types.VARCHAR);
            call.execute();
            String result = call.getString(5);
            System.out.print("R_BIZ_ABLSFPK..." + result);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource(null, call, con);
        }
        return "-1";
    }

    /**
     * 无保证金模式，进行配款
     *
     * @param xsdId
     * @param khId
     * @return
     */
    public String wbzjPk(Long xsdId, Long xshtId, Long khId, Long cghtId) {
        Connection con = null;
        CallableStatement call = null;
        String result;
        try {
            con = getSession().connection();
            System.out.print("R_BIZ_WBZJPK...");
            call = con.prepareCall("{Call R_BIZ_WBZJPK(?,?,?,?,?)}");
            call.setLong(1, xsdId);
            call.setLong(2, xshtId);
            call.setLong(3, khId);
            call.setLong(4, cghtId);
            call.registerOutParameter(5, Types.VARCHAR);
            call.execute();
            result = call.getString(5);
            System.out.print("R_BIZ_WBZJPK..." + result);
            return result;
        } catch (Exception e) {
            e.getMessage();
        } finally {
            closeResource(null, call, con);
        }
        return "-1";
    }

    /**
     * 找到客户的全部贴息金额
     *
     * @param kh
     * @return
     */
    public List<Map> findSumKhTx(Gsjg kh, String xsdIds) {
        String sql = "select nvl(sum(sfk.txye), 0) tx, 0 as dlf, 0 as zjzyf from t_biz_sfk sfk  " +
                " where txye > 0 and sfjstx = '1' and gldw_id = ?  and sfk.id in (select distinct jf.sfk_id from t_biz_jfmx jf where jf.xsd_id in (" + xsdIds + "))";
        return (List<Map>) this.createSQLQuery(sql, kh.getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Map> findAllSfkByKh(String xsdIds, Long xsjsdId) {
        if (null == xsjsdId) {
            String sql = "select sfk.id from t_biz_sfk sfk  " +
                    " where txye > 0 and sfjstx = '1' and sfk.id in (select distinct jf.sfk_id from t_biz_jfmx jf where jf.xsd_id in (" + xsdIds + "))";
            return (List<Map>) this.createSQLQuery(sql).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        } else {
            String sql = "select sfk.id from t_biz_sfk sfk  " +
                    " where txye > 0 and sfjstx = '1' and sfk.jsd_id = ? and sfk.id in (select distinct jf.sfk_id from t_biz_jfmx jf where jf.xsd_id in(" + xsdIds + "))";
            return (List<Map>) this.createSQLQuery(sql, xsjsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        }
    }

    public List<Sfk> findAllBzj(Qyjg gsjg) {
        String hql = "from Sfk where gldw.id = ? and zt = 'ytz' and  kxsy.id = ? order by ye ";
        return this.find(hql, gsjg.getId(), 121191L);
    }
}
